package model;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ClienteImpl implements Cliente {
	private Connection conn;

	public ClienteImpl(Connection conn) throws SQLException, IOException {
		this.conn = conn;
	}

	public boolean sogliaCliente(int idCliente) throws SQLException {
		String query = "SELECT numLibri FROM Cliente WHERE idCliente = ?";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setLong(1, idCliente);
		ResultSet rs = stat.executeQuery();
		rs.next();
		int risultato = rs.getInt("numLibri");
		stat.close();
		if (risultato >= 3)
			return false;
		return true;
	}

	public String getMail(int idCliente) throws SQLException {
		String query = "SELECT email FROM Cliente WHERE idCliente = ?";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setLong(1, idCliente);
		ResultSet rs = stat.executeQuery();
		rs.next();
		return rs.getString(1);
	}

	public String getPassword(int idCliente) throws SQLException {
		String query = "SELECT password FROM Cliente WHERE idCliente = ?";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setInt(1, idCliente);
		ResultSet rs = stat.executeQuery();
		rs.next();
		return rs.getString(1);
	}

	public void setNumCopieInPrestito(int numCopie, int idCliente)
			throws SQLException {
		String ps = "UPDATE Cliente SET numLibri = ? WHERE idCliente = ?";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setInt(1, numCopie);
		stat.setInt(2, idCliente);
		stat.executeUpdate();
		stat.close();
	}

	public void rimuoviCliente(int idCliente) throws SQLException {
		String query = "DELETE from Cliente WHERE idCliente = ?";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setLong(1, idCliente);
		stat.executeUpdate();
		stat.close();
	}

	public void setMail(String mail, int idCliente) throws SQLException {
		String query = "UPDATE Cliente SET email=? WHERE idCliente = ?";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setString(1, mail);
		stat.setInt(2, idCliente);
		stat.executeUpdate();
		stat.close();
	}

	@Override
	public ResultSet mioStorico(String order, int idAccesso)
			throws SQLException {
		String query = "SELECT * from StoricoCliente,Libri WHERE StoricoCliente.Libri_isbn = Libri.isbn AND Cliente_idCliente=? ORDER BY "
				+ order;
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setInt(1, idAccesso);
		ResultSet rs = stat.executeQuery();
		return rs;
	}

	public ResultSet storicoClienti(String order) throws SQLException {
		String query = "SELECT * from StoricoCliente,Libri WHERE StoricoCliente.Libri_isbn = Libri.isbn ORDER BY "
				+ order;
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(query);

		return rs;
	}

	@Override
	public void aggiungiCliente(String nome, String cognome, String mail,
			String pass) throws SQLException {
		String ps = "INSERT into Cliente values (0,?,?,?,0,?)";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setString(1, nome);
		stat.setString(2, cognome);
		stat.setString(3, mail);
		stat.setString(4, pass);
		stat.executeUpdate();
		stat.close();
	}

	public ResultSet cercaCliente(String[] parametri, String[] nomiParametri)
			throws SQLException {
		StringBuilder sb = new StringBuilder(300);
		sb.append("SELECT * FROM Cliente where ");
		int c = 0;
		for (int i = 0; i < parametri.length; i++) {
			if (!parametri[i].equals("null") && parametri[i].length() >= 1) {
				if (i == 0 || i == 3) {
					sb.append(nomiParametri[i]);
					sb.append(" = '");
					sb.append(parametri[i]);
					sb.append("' AND ");
				} else {
					sb.append(nomiParametri[i]);
					sb.append(" LIKE  '%");
					sb.append(parametri[i]);
					sb.append("%'");
					sb.append(" AND ");
				}
				c++;
			}
		}
		if (c > 0)
			sb.append(" 1=1");
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sb.toString());
		return rs;
	}

	@Override
	public int copieCliente(int idCliente) throws SQLException {
		String ps = "SELECT numLibri from Cliente WHERE idCliente = ?";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setInt(1, idCliente);
		ResultSet rs = stat.executeQuery();
		rs.next();
		return rs.getInt(1);
	}

	@Override
	public void incrementaCopie(int idCliente) throws SQLException {
		String ps = "UPDATE Cliente SET numLibri = numLibri + 1  WHERE idCliente = ? ";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setInt(1, idCliente);
		stat.executeUpdate();
		stat.close();
	}

	@Override
	public void decrementaCopie(long isbn, int idCliente) throws SQLException {
		String ps = "UPDATE Cliente SET numLibri = numLibri - 1  WHERE idCliente = ? ";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setInt(1, idCliente);
		stat.executeUpdate();
		stat.close();
	}

	public String[] getCredenziali(int idCliente) throws SQLException {
		String query = "SELECT nome,cognome,email FROM Cliente WHERE idCliente = ?";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setLong(1, idCliente);
		ResultSet rs = stat.executeQuery();
		rs.next();
		String[] credenziali = new String[3];
		credenziali[0] = rs.getString("nome").toUpperCase();
		credenziali[1] = rs.getString("cognome").toUpperCase();
		credenziali[2] = rs.getString("email");
		return credenziali;

	}

	@Override
	public int ultimoIdCliente() throws SQLException {
		String query = "SELECT Max(idCliente) from Cliente";
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(query);
		rs.next();
		return rs.getInt(1);
	}

	@Override
	public ResultSet daRestituire() throws SQLException {
		String query = "select * from StoricoCliente,Cliente where idCliente=StoricoCliente.Cliente_idCliente having dataStimata<curdate()	and dataReale is null";
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(query);
		return rs;
	}

	@Override
	public ResultSet storicoClienti(int numCopie) throws SQLException {
		String query = "SELECT titolo as Titolo,count(*) as Numero,isbn,numCopie from StoricoCliente,Libri WHERE StoricoCliente.Libri_isbn = Libri.isbn group by isbn ORDER BY Numero desc Limit "
				+ numCopie;
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(query);
		return rs;
	}

	@Override
	public String getGenerePreferito(int idCliente) throws SQLException {
		String query = "SELECT genere from StoricoCliente,Libri WHERE StoricoCliente.Libri_isbn = Libri.isbn  and Cliente_idCliente= ? ORDER BY count(*) desc Limit 1";
		PreparedStatement stat = conn.prepareStatement(query);
		stat.setInt(1, idCliente);
		ResultSet rs = stat.executeQuery();
		rs.next();
		String ris = rs.getString("genere");
		return ris;
	}

	@Override
	public boolean esisteCliente(int idCliente) throws SQLException {
		String ps = "SELECT *  from Cliente WHERE idCliente = ?";
		PreparedStatement stat = conn.prepareStatement(ps);
		stat.setLong(1, idCliente);
		ResultSet rs = stat.executeQuery();
		return rs.next();
	}

}
